Connecting to Salesforce

FastStats Designer can extract data from Salesforce. To do this it uses the Bulk Query functionality of the Salesforce Bulk API V2 to maximise performance and minimise the number API calls to Salesforce.

The Pre-Load Action 'Download data from Salesforce' has been deprecated.

Check that your Salesforce edition allows API access: https://help.salesforce.com/articleView?id=000326486

Giving FastStats Designer access to your Salesforce account

To add a connection to Salesforce you first must first register FastStats Designer with your Salesforce account as a 'Connected App'.

https://developer.salesforce.com/docs/atlas.en-us.api_rest.meta/api_rest/intro_defining_remote_access_applications.htm

  1. Log in to Salesforce and navigate to the App Manager. Create a 'New Connected App'.
  2. Check the OAuth settings check box.
  3. Enter a Callback URL (it possible to specify up to 10 valid call back URLs). The default is https://faststats/callback
  4. Ensure that the following OAuth Scopes are selected:
    • Access and manage your data (api)
    • Perform requests on your behalf at any time (refresh_token, offline_access)
  5. Click 'Save' to create the new connected app
  6. The Consumer Key (also called the Client ID) and the Consumer Secret (also called the Client Secret) are generated by Salesforce and must be entered into Designer.
  7. Click to 'Manage' the new connected app
    • Click 'Edit Policies'
    • Set the Refresh Token Policy to 'Refresh token is valid until revoked'

Add a Salesforce Credential

Your Salesforce account and authentication details are stored in the 'Credentials' section of the System Configuration. Navigate to the 'Credentials' tab in the System Configuration Window and

Choose 'New', give your credential a name and choose 'Salesforce' as the credential type.

Select the new credential and choose 'Edit':

Enter the Salesforce Client Id, Client Secret and Callback Uri defined in the Salesforce Connected App settings.

Two authentication methods are supported:

OAuth Token

Click 'Get OAuth Token', FastStats Designer will open a dialog containing a Web Browser. Use this browser to sign in to the Salesforce web site. Salesforce will then return a Refresh Token to FastStats Designer. This Refresh Token is stored in the design and used instead of a username & password to generate a temporary Access Token for each connected session.

If the embedded browser fails to display the login page, then double-check the Callback URL is specified in Designer exactly as it is defined in Salesforce. If embedded browser still fails to display you may have to use the Username & Password authentication method.

Username & Password

A username, password and security token can be stored in the design XML. FastStats Designer will then use these credentials to log into Salesforce for each session.

Add a Salesforce Data Source

Navigate to the Data Sources Window and choose 'Add Data Source'. Select 'Salesforce' and click OK. Give your new data source a name (this will become a table name in FastStats).

To retrieve data from Salesforce choose 'Properties':

The <limit> placeholder will be replaced by 'LIMIT 100' for preview data if it is specified but removed completely during the extract process.

You can then choose the Credential you want to connect with and then click 'Update List', choose a Salesforce object and click OK to connect to Salesforce and retrieve a preview of the data.

You can specify a filter that will be passed as the WHERE clause in the SELECT query. This filter is in Salesforce SOQL syntax.

You can also specify a Custom SOQL query to retrieve the fields you want:

Metadata Support

Salesforce objects and fields are packaged with metadata. FastStats Designer will use this metadata to aid defining a system:

Salesforce data types are used to set initial FastStats data types. Pick list fields are convert to Selectors and the decodes are automatically set.

Adding a table to the folder structure will auto group variable into a basic folder structure. FastStats Designer uses a number of heuristics to group keys into a folder, group variables involved in composite fields into folders and to group audit fields into folders.

Time Zone

Salesforce stores all DateTime variables in (Universal Coordinated Time) UTC. During extract FastStats Designer will convert this to a local time zone. By default Designer will convert the datetimes to the Time Zone that the build machine is set to, but it is possible to override this by checking the 'Time zone for extracts' check box and choosing a Time Zone from the list. The Time Zone used will also apply the offset for Daylight Savings Time (DST).

Delta Extract Support

If the Enhanced Data Build process is being used then Salesforce data source can be specified as Delta update data sources where only record changes (appends, updates, deletes) are downloaded.

The Salesforce IsDeleted flag is used by default as the Delta delete indicator. This flag is true if the record is in the Salesforce recycle bin. Records are only present in the recycle bin for a limited period of time (15 days), so if the build isn't built within this time frame then the delta process will not know about the deleted records that left the recycle bin and the records will continue to be present in the FastStats system.

The Salesforce SystemModStamp field is used as the ERN (Extract Record Number), FastStats Designer automatically manages this field for you if you specify it in your Where Filter or in your Custom SOQL query:

Instead of an ERN of -1 FastStats Designer will use a SystemModStamp value of 01/01/1000 which is the smallest DateTime value that Salesforce recognises. The SystemModstamp is always displayed and extracted as UTC, it is not modified by the time zone.